package com.cooperate.fly.service.buildDB.impl;


import com.cooperate.fly.bo.*;
import com.cooperate.fly.mapper.*;
import com.cooperate.fly.service.buildDB.BuildCaseService;
import com.cooperate.fly.service.system.Constant;
import com.cooperate.fly.util.FileUtil;
import com.cooperate.fly.util.ZipUtil;
import com.cooperate.fly.web.*;
import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonParser;
import com.meterware.httpunit.scripting.Input;
import com.sun.xml.internal.ws.api.pipe.FiberContextSwitchInterceptor;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import org.apache.tools.ant.types.selectors.WritableSelector;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.text.DateFormat;
import java.util.*;
import java.util.zip.ZipEntry;
import java.util.zip.ZipFile;
import java.util.zip.ZipInputStream;
import java.util.zip.ZipOutputStream;

/**
 * Created by Stellar_Lee on 2016/4/18.
 */
@Service("build_case")
public class BuildCaseServiceImpl implements BuildCaseService {

    @Autowired
    CaseMapper caseMapper;
    @Autowired
    SchemaMapper schemaMapper;
    @Autowired
    CatalogInfoMapper catalogInfoMapper;

    @Resource
    private DataRightMapper dataRightMapper;

    @Autowired
    FieldMapper fieldMapper;
    @Autowired
    TableMapper tableMapper;
    @Resource
    private WebFrontHelper webFrontHelper;

    @Value("#{propertiesReader['dWriteAuth']}")
    public int writeAuth;

    @Value("#{propertiesReader['create_success']}")
    private String createSuccess;
    @Value("#{propertiesReader['create_noConstruct']}")
    private String createNoConstruct;
    @Value("#{propertiesReader['delete_success']}")
    private String deleteSuccess;
    @Value("#{propertiesReader['update_success']}")
    private String updateSuccess;
    @Value("#{propertiesReader['create_same_name_node']}")
    private String sameNodeError;
    @Value("#{propertiesReader['case_catalog_type']}")
    private Integer caseCatalogType;
    @Value("#{propertiesReader['case_data_type']}")
    private Integer caseDataType;

    @Value("#{propertiesReader['case_data_root_name']}")
    public String caseDataRootName;
    @Value("#{propertiesReader['case_data_root_icon']}")
    public String caseDataRootIcon;

    @Value("#{propertiesReader['field_catalog_type']}")
    public int fieldCatalogType;
    @Value("#{propertiesReader['field_data_float_type']}")
    public int fieldFloatType;
    @Value("#{propertiesReader['field_data_text_type']}")
    public int fieldTextType;
    @Value("#{propertiesReader['field_data_picture_type']}")
    public int fieldPicType;
    @Value("#{propertiesReader['field_data_matrix_type']}")
    public int fieldMatrixType;
    @Value("#{propertiesReader['field_data_attach_type']}")
    public int fieldAttachType;
    @Value("#{propertiesReader['field_data_3D_type']}")
    public  int field3DType;
    @Value("#{propertiesReader['field_data_video_type']}")
    public  int fieldVideoType;
    @Value("#{propertiesReader['field_data_multi_type']}")
    public int fieldMultiType;
    @Value("#{propertiesReader['field_data_time_type']}")
    public int fieldTimeType;
    @Value("#{propertiesReader['field_data_link_type']}")
    public int fieldLinkType;

    @Value("#{propertiesReader['field_catalog_name']}")
    public String fieldCatalogName;
    @Value("#{propertiesReader['field_data_float_name']}")
    public String fieldFloatName;
    @Value("#{propertiesReader['field_data_text_name']}")
    public String fieldTextName;
    @Value("#{propertiesReader['field_data_picture_name']}")
    public String fieldPicName;
    @Value("#{propertiesReader['field_data_matrix_name']}")
    public String fieldMatrixName;
    @Value("#{propertiesReader['field_data_attach_name']}")
    public String fieldAttachName;
    @Value("#{propertiesReader['field_data_multi_name']}")
    public String fieldMultiName;
    @Value("#{propertiesReader['field_data_3D_name']}")
    public String field3DName;
    @Value("#{propertiesReader['field_data_video_name']}")
    public String fieldVideoName;
    @Value("#{propertiesReader['field_data_time_name']}")
    public String fieldTimeName;
    @Value("#{propertiesReader['field_data_link_name']}")
    public String fieldLinkName;

    @Value("#{propertiesReader['case_catalog_icon']}")
    public String caseCatalogIcon;
    @Value("#{propertiesReader['case_data_icon']}")
    public String caseDataIcon;
    @Value("#{propertiesReader['field_catalog_icon']}")
    public String fieldCatalogIcon;
    @Value("#{propertiesReader['field_float_icon']}")
    public String fieldFloatIcon;
    @Value("#{propertiesReader['field_text_icon']}")
    public String fieldTextIcon;
    @Value("#{propertiesReader['field_picture_icon']}")
    public String fieldPicIcon;
    @Value("#{propertiesReader['field_attach_icon']}")
    public String fieldAttachIcon;
    @Value("#{propertiesReader['field_matrix_icon']}")
    public String fieldMatrixIcon;
    @Value("#{propertiesReader['field_multi_icon']}")
    public String fieldMultiIcon;

    @Value("#{propertiesReader['url_split_symbol']}")
    public String urlSplitSymbol;

    @Value("#{propertiesReader['filePath']}")
    public String filePath;
    @Value("#{propertiesReader['imagePath']}")
    public String imagePath;
    @Value("#{propertiesReader['threeDPath']}")
    public String threeDPath;
    @Value("#{propertiesReader['videoPath']}")
    public String videoPath;
    @Value("#{propertiesReader['caseTempPath']}")
    public String caseTempPath;
    @Value("#{propertiesReader['casesTempPath']}")
    public String casesTempPath;

    @Value("#{propertiesReader['exportExcelTitle1']}")
    public String excelTitle1;
    @Value("#{propertiesReader['exportExcelTitle2']}")
    public String excelTitle2;
    @Value("#{propertiesReader['exportExcelTitle3']}")
    public String excelTitle3;
    @Value("#{propertiesReader['exportExcelTitle4']}")
    public String excelTitle4;
    @Value("#{propertiesReader['exportExcelTitle5']}")
    public String excelTitle5;
    @Value("#{propertiesReader['exportExcelTitle6']}")
    public String excelTitle6;
    @Value("#{propertiesReader['exportExcelRow2_1']}")
    public String excelRow2_1;
    @Value("#{propertiesReader['exportExcelRow2_2']}")
    public String excelRow2_2;







    @Override
    public Result createCaseNode(Case _case) {
        Result res = new Result();

        //设置_case的schemaId
        if(_case.getSchemaId()==null) {
            int parentId = _case.getParentId();
            Case parentCase = caseMapper.selectByPrimaryKey(parentId);
            if(parentCase==null){
                _case.setSchemaId(parentId);
            }else {
                _case.setSchemaId(parentCase.getSchemaId());
            }
        }
        int flag = 0;
        try{
            TableWithBLOBs testrecord = new TableWithBLOBs();
            testrecord.setFieldId(Integer.MAX_VALUE);
            testrecord.setCaseId(Integer.MAX_VALUE);
            int teststatus = tableMapper.insertIntoTable("table_" + _case.getSchemaId(), testrecord);
            flag = 1;
        }catch(Exception e){
        }
        if(flag==0){
            res.setMessage(createNoConstruct);
            res.setSuccessful(false);
            return res;
        }else{
            tableMapper.deleteByCaseId("table_" + _case.getSchemaId(), Integer.MAX_VALUE);
        }
        if(_case.getName()!=null)
            caseMapper.insert(_case);
        int id = _case.getId();
        if(!checkName(_case)){
            caseMapper.deleteByPrimaryKey(id);
            res.setMessage(sameNodeError);
            res.setSuccessful(false);
            return res;
        }
        // add case path
        String path = getCasePath(id);
        caseMapper.updatePath(path, id);

        if(_case.getType() == caseDataType) {
            List<Field> fieldList = fieldMapper.selectBySchemaId(_case.getSchemaId());
            //初始化记录插入table_schemaId，value为“”
            for(Field field : fieldList) {
                TableWithBLOBs record = new TableWithBLOBs();
                record.setFieldId(field.getId());
                record.setCaseId(id);
                record.setValue(" ");
                record.setRemark2(field.getRemark3()+" ");
                int status = tableMapper.insertIntoTable("table_" + _case.getSchemaId(), record);
                if(status==0){
                    res.setMessage(createNoConstruct);
                    res.setSuccessful(false);
                    return res;
                }
            }
        }
        //如果创建的是实例分类节点，则还需要插入CatalogInfo表
        if(_case.getType() == caseCatalogType){
            CatalogInfo catalogInfoRecord = new CatalogInfo();
            catalogInfoRecord.setCatalogId(id);
            catalogInfoMapper.insert(catalogInfoRecord);
        }

        res.setMessage(createSuccess);
        res.setSuccessful(true);
        res.setData(id);
        return res;
    }


    @Override
    public Result deleteCaseNode(Case _case) {

        return null;
    }

    @Override
    public Result deleteCaseNodeById(int id) {
        Result res = new Result();
        Case _case = caseMapper.selectByPrimaryKey(id);
        caseMapper.deleteByPrimaryKey(id);
        if(_case.getType()==caseDataType){
            //删除table_schemaId中对应caseId的数据
            tableMapper.deleteByCaseId("table_"+_case.getSchemaId(),id);
        }else{
            List<CaseWithBLOBs> sonCases = caseMapper.selectByParentId(id);
            for(CaseWithBLOBs son : sonCases){
                caseMapper.deleteByPrimaryKey(son.getId());
                if(son.getType()==caseDataType){
                    tableMapper.deleteByCaseId("table_"+_case.getSchemaId(),son.getId());
                }
            }
        }
        res.setMessage(deleteSuccess);
        return res;
    }

    @Override
    public Result updateCaseNode(Case _case) {
        Result res = new Result();
        if(!checkName(_case)){
            res.setMessage(sameNodeError);
            res.setSuccessful(false);
            return res;
        }
        caseMapper.updateCaseName(_case);
        res.setMessage(updateSuccess);
        return res;
    }

    @Override
    public Result searchCaseNode(Case _case) {
        return null;
    }

    @Override
    public Result searchCaseNodeById(int id) {
        return null;
    }

    @Override
    public List<Case> getAllCases(){

        return caseMapper.selectAll();
    }

    private List<CaseDataTreeNode> buildCaseFieldData(int caseId) {
        long start = System.currentTimeMillis();
        Case _case = caseMapper.selectByPrimaryKey(caseId);
        int schemaId = _case.getSchemaId();
        long end = System.currentTimeMillis();
        System.out.println("select case : "+(end-start));
//        List<Field> fieldList = fieldMapper.selectBySchemaId(schemaId);
        start = System.currentTimeMillis();
        String table = "table_".concat(Integer.toString(schemaId));
        end = System.currentTimeMillis();
        System.out.println("concat table name:" + (end-start));

        start = System.currentTimeMillis();
        List<TableWithBLOBs> valueList = tableMapper.selectByCaseId(table, caseId);
//        for(TableWithBLOBs value : valueList){
//            fieldList.add(fieldMapper.selectByPrimaryKey(value.getFieldId()));
//        }
        end = System.currentTimeMillis();
        System.out.println("select value:"+(end-start));

        start = System.currentTimeMillis();
        List<Field> fieldList = fieldMapper.selectBySchemaId(schemaId);
        end = System.currentTimeMillis();
        System.out.println("select field:"+(end-start));
//        for(Field field : fieldList){
//            if(field.getOrder()==null){
//                field.setOrder(0);
//            }
//        }
        start = System.currentTimeMillis();
        Collections.sort(fieldList, new Comparator<Field>() {
            @Override
            public int compare(Field o1, Field o2) {
                return o1.getOrder().compareTo(o2.getOrder());
            }
        });
        end = System.currentTimeMillis();
        System.out.println("sort:"+(end-start));

        start = System.currentTimeMillis();
        CaseDataTreeNode root = webFrontHelper.buildCaseDataTree(fieldList, null,_case.getName(),caseDataRootIcon,valueList);
        end = System.currentTimeMillis();
        System.out.println("construct tree:"+(end-start));
        List<CaseDataTreeNode> list = new ArrayList<CaseDataTreeNode>();
        list.add(root);
        return list;

    }

    @Override
    public String buildCaseFieldTree(int caseId) {
        List<CaseDataTreeNode> list = buildCaseFieldData(caseId);
        return new Gson().toJson(list);
    }

    @Override
    public String getDataBaseCases(int dataBaseId) {
        List<CaseWithBLOBs> list = caseMapper.selectBySchemaId(dataBaseId, caseDataType);
        return createCaseTableJson(list);
    }

    @Override
    public String getCatalogCases(int caseId) {
        List<CaseWithBLOBs> list = caseMapper.selectByParentId(caseId);
        List<CaseWithBLOBs> caseList = new ArrayList<CaseWithBLOBs>();
        for(CaseWithBLOBs node : list){
            if(node.getType()==caseDataType){
                caseList.add(node);
            }
        }
        return createCaseTableJson(caseList);
    }

    private String createCaseTableJson(List<CaseWithBLOBs> list){
        List<CaseTable> caseTableList = new ArrayList<CaseTable>();
        for(CaseWithBLOBs record : list){
            CaseTable row = new CaseTable();
            row.setId(record.getId());
            row.setName(record.getName());
            row.setReviser(record.getReviser());
            row.setTime(DateFormat.getDateInstance().format(record.getUpdateTime()));
            caseTableList.add(row);
        }
        return new Gson().toJson(caseTableList);
    }

    @Override
    public String visitCases(String schemaNamesPath) {
        String[] schemaNames = schemaNamesPath.split(urlSplitSymbol);
        int dataBaseId = getSchemaId(schemaNames);
        if(dataBaseId == -1) return null;
        List<CaseWithBLOBs> caseList = caseMapper.selectBySchemaId(dataBaseId, caseCatalogType);
        caseList.addAll(caseMapper.selectBySchemaId(dataBaseId, caseDataType));
        if(caseList != null) {
            return new Gson().toJson(caseList);
        }
        return null;
    }

    @Override
    public String visitCase(String schemaNamesPath, String caseNamesPath) {
        String[] schemaNames = schemaNamesPath.split(urlSplitSymbol);
        String[] caseNames = caseNamesPath.split(urlSplitSymbol);
        int dataBaseId = getSchemaId(schemaNames);
        if(dataBaseId == -1) return null;
        int caseId = getCaseId(caseNames, dataBaseId);
        if(caseId == -1) return null;
        return buildCaseFieldTree(caseId);
    }

    @Override
    public String visitCaseById(int caseId){
        return buildCaseFieldTree(caseId);
    }

    @Override
    public String visitField(String schemaNamesPath, String caseNamesPath, String fieldNamesPath) {
        String[] schemaNames = schemaNamesPath.split(urlSplitSymbol);
        String[] caseNames = caseNamesPath.split(urlSplitSymbol);
        String[] fieldNames = fieldNamesPath.split(urlSplitSymbol);
        int dataBaseId = getSchemaId(schemaNames);
        if(dataBaseId == -1) return null;
        int caseId = getCaseId(caseNames, dataBaseId);
        if(caseId == -1) return null;
        int fieldId = getFieldId(fieldNames, dataBaseId);
        if(fieldId == -1) return null;
        TableWithBLOBs record = tableMapper.selectByCaseIdAndFieldId("table_"+dataBaseId, caseId, fieldId);
        if(record == null) return null;
        return record.getValue();
    }

    @Override
    public String visitFields(String schemaNamesPath, String caseNamesPath, String fieldNamesPath) {
        String[] schemaNames = schemaNamesPath.split(urlSplitSymbol);
        String[] caseNames = caseNamesPath.split(urlSplitSymbol);
        int dataBaseId = getSchemaId(schemaNames);
        if(dataBaseId == -1) return null;
        int caseId = getCaseId(caseNames, dataBaseId);
        if(caseId == -1) return null;
        String[] fieldNamesGroup = fieldNamesPath.split("_");
        StringBuilder res = new StringBuilder();
        for(int i=0;i<fieldNamesGroup.length;i++){
            String[] fieldNames = fieldNamesGroup[i].split(urlSplitSymbol);
            int fieldId = getFieldId(fieldNames, dataBaseId);
            if(fieldId == -1) return null;
            TableWithBLOBs record = tableMapper.selectByCaseIdAndFieldId("table_"+dataBaseId, caseId, fieldId);
            if(record == null) return null;
            res.append(record.getValue()+"#");
        }

        return res.deleteCharAt(res.length()-1).toString();
    }

    @Override
    public boolean loadExcel(String filePath, int caseId){
        int schemaId = caseMapper.selectByPrimaryKey(caseId).getSchemaId();
        List<Field> fields = fieldMapper.selectBySchemaId(schemaId);
        Collections.sort(fields, new Comparator<Field>() {
            @Override
            public int compare(Field o1, Field o2) {
                return o1.getOrder().compareTo(o2.getOrder());
            }
        });
        try{
            InputStream is = new FileInputStream(filePath);
            Workbook wb = Workbook.getWorkbook(is);
            Sheet st = wb.getSheet(0);
            int cols = st.getColumns();
            int rows = st.getRows();
            //System.out.println(rows);
            if(rows-2 != fields.size()){
                return false;
            }
            for(int i=2;i<rows;i++){ //ignore row0 and row1
                if(!st.getCell(0,i).getContents().equals(fields.get(i-2).getName())){
                    return false;
                }
            }
            for(int i=2;i<rows;i++){
                String value = st.getCell(4, i).getContents()==""?" ":st.getCell(4, i).getContents();
                //System.out.println(value);
                String remark = st.getCell(5, i).getContents()==""?" ":st.getCell(5, i).getContents();
                //System.out.println(remark);
                int fieldId = fields.get(i-2).getId();
                tableMapper.updateValueandRemark("table_" + schemaId, value, remark, fieldId, caseId);
            }
            wb.close();
            is.close();
        }catch(Exception e){

        }
        return true;
    }

    private void createDir(String caseDirPath, Case _case){
        int caseId = _case.getId();
        File caseDir = new File(caseDirPath);
        caseDir.mkdir();
        int schemaId = _case.getSchemaId();
        List<Field> fields = fieldMapper.selectBySchemaId(schemaId);
        Queue<File> queue = new LinkedList<File>();
        Queue<Field> queue2 = new LinkedList<Field>();
        for(Field field : fields){
            if(field.getType()==fieldCatalogType && field.getParentId()==1){
                queue.offer(new File(caseDirPath+File.separator+field.getName()));
                queue2.offer(field);
            }
        }
        if(queue.isEmpty()){ // no fieldCatalog
            for(Field field : fields){
                String curPath = null;
                String originPath = null;
                if(field.getType()==fieldAttachType){
                    curPath = caseDirPath+File.separator+field.getName()+"_file";
                    originPath = filePath;
                }else if(field.getType()==fieldPicType){
                    curPath = caseDirPath+File.separator+field.getName()+"_image";
                    originPath = imagePath;
                }else if(field.getType()==field3DType){
                    curPath = caseDirPath+File.separator+field.getName()+"_3D";
                    originPath = threeDPath;
                }else if(field.getType()==fieldVideoType){
                    curPath = caseDirPath+File.separator+field.getName()+"_video";
                    originPath = videoPath;
                }else if(field.getType()==fieldMultiType){
                    curPath = caseDirPath+File.separator+field.getName()+"_"+field.getId()+"_multi";
                }
                new File(curPath).mkdir();
                if(field.getType()==fieldMultiType){
                    File file = new File(curPath+File.separator+field.getName()+".html");
                    try {
                        file.createNewFile();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                    String value = tableMapper.selectByCaseIdAndFieldId("table_" + schemaId, caseId, field.getId()).getValue();
                    FileUtil.writeToFile(value, file);
                }else {
                    String filenames = tableMapper.selectByCaseIdAndFieldId("table_" + schemaId, caseId, field.getId()).getValue();
                    if (filenames == null || filenames == "" || filenames == " ") continue;
                    String[] filenamesArr = filenames.split(";");
                    if (filenamesArr.length == 1) {
                        FileUtil.copyFile(new File(originPath + File.separator + filenamesArr[0]), new File(curPath + File.separator + filenamesArr[0]));
                    } else {
                        for (int i = 1; i < filenamesArr.length; i++) {
                            FileUtil.copyFile(new File(originPath + File.separator + filenamesArr[i]), new File(curPath + File.separator + filenamesArr[i]));
                        }
                    }
                }
            }
        }else{ //have fieldCatalog
            while(!queue.isEmpty()) {
                File curDir = queue.poll();
                curDir.mkdir();
                Field curField = queue2.poll();
                List<Field> subFields = fieldMapper.selectByParentId(curField.getId());
                if(subFields==null) continue;
                for(Field field : subFields){
                    if(field.getType()==fieldCatalogType){
                        queue.offer(new File(curDir.getAbsolutePath() + File.separator + field.getName()));
                        queue2.offer(field);
                    }else if(field.getType()==fieldMultiType || field.getType()==fieldAttachType || field.getType()==fieldPicType || field.getType()==field3DType || field.getType()==fieldVideoType){
                        String curPath = null;
                        String originPath = null;
                        if(field.getType()==fieldAttachType){
                            curPath = curDir.getAbsolutePath()+File.separator+field.getName()+"_file";
                            originPath = filePath;
                        }else if(field.getType()==fieldPicType){
                            curPath = curDir.getAbsolutePath()+File.separator+field.getName()+"_image";
                            originPath = imagePath;
                        }else if(field.getType()==field3DType){
                            curPath = curDir.getAbsolutePath()+File.separator+field.getName()+"_3D";
                            originPath = threeDPath;
                        }else if(field.getType()==fieldVideoType){
                            curPath = curDir.getAbsolutePath()+File.separator+field.getName()+"_video";
                            originPath = videoPath;
                        }else if(field.getType()==fieldMultiType){
                            curPath = curDir.getAbsolutePath()+File.separator+field.getName()+"_"+field.getId()+"_multi";
                        }
                        new File(curPath).mkdir();
                        if(field.getType()==fieldMultiType){
                            File file = new File(curPath+File.separator+field.getName()+".html");
                            try {
                                file.createNewFile();
                            } catch (IOException e) {
                                e.printStackTrace();
                            }
                            String value = tableMapper.selectByCaseIdAndFieldId("table_" + schemaId, caseId, field.getId()).getValue();
                            FileUtil.writeToFile(value, file);
                        }else {
                            String filenames = tableMapper.selectByCaseIdAndFieldId("table_" + schemaId, caseId, field.getId()).getValue();
                            if (filenames == null || filenames.equals("") || filenames.equals(" ")) continue;
                            String[] filenamesArr = filenames.split(";");
                            if (filenamesArr.length == 1) {
                                FileUtil.copyFile(new File(originPath + File.separator + filenamesArr[0]), new File(curPath + File.separator + filenamesArr[0]));
                            } else {
                                for (int i = 1; i < filenamesArr.length; i++) {
                                    FileUtil.copyFile(new File(originPath + File.separator + filenamesArr[i]), new File(curPath + File.separator + filenamesArr[i]));
                                }
                            }
                        }
                    }
                }
            }

        }
    }

    @Override
    public void createSpecialDataDir(int caseId){
        Case _case = caseMapper.selectByPrimaryKey(caseId);
        String basePath = caseTempPath + File.separator;
        String caseDirPath = basePath + _case.getName();
        createDir(caseDirPath, _case);
    }

    private void createExcel(String filepath, Case _case){
        int caseId = _case.getId();
        String[] title = {excelTitle1,excelTitle2,excelTitle3,excelTitle4,excelTitle5,excelTitle6};
        try {
            //create excel workbook
            WritableWorkbook wwb;
            OutputStream os = new FileOutputStream(filepath);
            wwb = Workbook.createWorkbook(os);
            //create sheet
            WritableSheet sheet = wwb.createSheet(_case.getName(), 0);
            //write header row
            for(int i=0;i<title.length;i++){
                Label label = new Label(i, 0, title[i], FileUtil.getHeader());
                sheet.addCell(label);
            }
            //write second row
            String[] row2 = {excelRow2_1,excelRow2_2,"","",_case.getName(),""};
            for(int i=0;i<title.length;i++){
                Label label = new Label(i, 1, row2[i], FileUtil.getHeader2());
                sheet.addCell(label);
            }
            int schemaId = _case.getSchemaId();
            List<Field> fields = fieldMapper.selectBySchemaId(schemaId);
            Collections.sort(fields, new Comparator<Field>() {
                @Override
                public int compare(Field o1, Field o2) {
                    return o1.getOrder().compareTo(o2.getOrder());
                }
            });
            Queue<Field> queue = new LinkedList<Field>();
            for(Field field : fields){
                queue.offer(field);
            }
            int rowIndex = 1;
            Map<Integer, String> typeNameMap = getTypeNameMap();
            while(!queue.isEmpty()){
                Field cur = queue.poll();
                rowIndex++;
                if(cur.getType()==fieldCatalogType && cur.getParentId()==1){
                    String name = cur.getName();
                    String unit = "";
                    String father = "";
                    String value = "";
                    String remark = "";
                    String[] row = {name,fieldCatalogName,unit,father,value,remark};
                    for(int i=0;i<row.length;i++){
                        Label label = new Label(i, rowIndex, row[i], FileUtil.getCatalog());
                        sheet.addCell(label);
                    }
                }else if(cur.getType()==fieldCatalogType && cur.getParentId()!=1){
                    String name = cur.getName();
                    String unit = "";
                    String father = fieldMapper.selectByPrimaryKey(cur.getParentId()).getName();
                    String value = "";
                    String remark = "";
                    String[] row = {name,fieldCatalogName,unit,father,value,remark};
                    for(int i=0;i<row.length;i++){
                        Label label = new Label(i, rowIndex, row[i], FileUtil.getCatalog());
                        sheet.addCell(label);
                    }
                }else{
                    TableWithBLOBs record = tableMapper.selectByCaseIdAndFieldId("table_" + schemaId, caseId, cur.getId());
                    String the_value = " ";
                    if(cur.getType()!=fieldMultiType){
                        the_value = record.getValue();
                    }
                    String name = cur.getName();
                    String typeName = typeNameMap.get(cur.getType());
                    String unit = record.getRemark2()==null?" ":(record.getRemark2().equals("null")?" ":record.getRemark2());
                    String father = fieldMapper.selectByPrimaryKey(cur.getParentId()).getName();
                    String value = the_value;
                    String remark = record.getRemark3();
                    String[] row = {name,typeName,unit,father,value,remark};
                    for(int i=0;i<row.length;i++){
                        Label label = new Label(i, rowIndex, row[i], FileUtil.getData());
                        sheet.addCell(label);
                    }
                }
//                List<Field> sub = fieldMapper.selectByParentId(cur.getId());
//                int flag = 0; //the type of fields in sub: all catalog, all data, catalog and data
//                if(sub.isEmpty()) continue;
//                if(sub.get(0).getType()!=fieldCatalogType) flag = 1;
//                for(Field field : sub){
//                    if(field.getType()==fieldCatalogType && flag==0){
//                        flag = 0;
//                    }else if((field.getType()==fieldCatalogType && flag==1) || (field.getType()!=fieldCatalogType && flag==0)){
//                        flag = 2;
//                        break;
//                    }else if(field.getType()!=fieldCatalogType && flag==1){
//                        flag = 1;
//                    }
//                }
//                if(flag==1){
//                    for(Field field : sub){
//                        rowIndex++;
//                        TableWithBLOBs record = tableMapper.selectByCaseIdAndFieldId("table_" + schemaId, caseId, field.getId());
//                        String the_value = " ";
//                        if(field.getType()!=fieldMultiType){
//                            the_value = record.getValue();
//                        }
//                        String name = field.getName();
//                        String typeName = typeNameMap.get(field.getType());
//                        String unit = record.getRemark2()==null?" ":(record.getRemark2().equals("null")?" ":record.getRemark2());
//                        String father = fieldMapper.selectByPrimaryKey(field.getParentId()).getName();
//                        String value = the_value;
//                        String remark = record.getRemark3();
//                        String[] row = {name,typeName,unit,father,value,remark};
//                        for(int i=0;i<row.length;i++){
//                            Label label = new Label(i, rowIndex, row[i], FileUtil.getData());
//                            sheet.addCell(label);
//                        }
//                    }
//                }else{
//                    for(Field field : fields){
//                        queue.offer(field);
//                    }
//                }
            }

            wwb.write();
            wwb.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    @Override
    public void createAndWriteExcel(int caseId){
        Case _case = caseMapper.selectByPrimaryKey(caseId);
        String filepath = caseTempPath + File.separator + _case.getName() + ".xls";
        createExcel(filepath, _case);
    }

    @Override
    public void createSpecialDataDirForCases(int parentId){
        List<CaseWithBLOBs> caseList = caseMapper.selectByParentId(parentId);
        String base_path = casesTempPath;
//        System.out.println("base:"+base_path);
        Queue<CaseWithBLOBs> q1 = new LinkedList<CaseWithBLOBs>();
        Queue<File> q2 = new LinkedList<File>();
        for(CaseWithBLOBs _case : caseList){
            q1.offer(_case);
            if(_case.getType()==caseCatalogType){
                q2.offer(new File(base_path+File.separator+_case.getName()+"_catalog"));
            }else{
                q2.offer(new File(base_path+File.separator+_case.getName()+"_case"));
            }
        }
        while(!q1.isEmpty()){
            Case _case = q1.poll();
            File file = q2.poll();
            System.out.println(file.getAbsolutePath());
            if(_case.getType()==caseDataType){
                File _file = new File(file.getAbsolutePath()+File.separator+_case.getName());
                file.mkdir();
                createDir(_file.getAbsolutePath(),_case);
                createExcel(file.getAbsolutePath()+File.separator+_case.getName()+".xls", _case);
            }else{
                file.mkdir();
                List<CaseWithBLOBs> subcases = caseMapper.selectByParentId(_case.getId());
                for(CaseWithBLOBs subcase : subcases){
                    q1.offer(subcase);
                    if(subcase.getType()==caseCatalogType) {
                        q2.offer(new File(file.getAbsolutePath() + File.separator + subcase.getName() + "_catalog"));
                    }else{
                        q2.offer(new File(file.getAbsolutePath() + File.separator + subcase.getName() + "_case"));
                    }
                }
            }
        }

    }

    @Override
    public void createAndWriteExcelForCases(int parentId){

    }

    @Override
    public void updateMultiValue(int caseId, int fieldId, String value){
        int schemaId = caseMapper.selectByPrimaryKey(caseId).getSchemaId();
        tableMapper.updateValue("table_"+schemaId, value, fieldId, caseId);
    }

    @Override
    public String getImportTreeJson(int parentId){
        List<CaseWithBLOBs> caseList = caseMapper.selectByParentId(parentId);
        Queue<CaseWithBLOBs> catalogQueue = new LinkedList<CaseWithBLOBs>();
        List<CaseWithBLOBs> cases = new ArrayList<CaseWithBLOBs>();
        for(CaseWithBLOBs _case:caseList){
            catalogQueue.offer(_case);
        }
        while(!catalogQueue.isEmpty()){
            CaseWithBLOBs _case = catalogQueue.poll();
            cases.add(_case);
            if(_case.getType()==caseCatalogType){
                List<CaseWithBLOBs> subCases = caseMapper.selectByParentId(_case.getId());
                for(CaseWithBLOBs subcase : subCases){
                    catalogQueue.offer(subcase);
                }
            }
        }
        Map<Integer, TypeTreeNode> map = new LinkedHashMap<Integer, TypeTreeNode>();
        for(CaseWithBLOBs case_ : cases){
            TypeTreeNode treeNode = new TypeTreeNode();
            treeNode.setId(case_.getId());
            treeNode.setParentId(case_.getParentId());
            treeNode.setText(case_.getName());
            if(case_.getType()==caseCatalogType){
                treeNode.setAttributes(caseCatalogType+"");
                treeNode.setIconCls(caseCatalogIcon);
            }else{
                treeNode.setAttributes(caseDataType+"");
                treeNode.setIconCls(caseDataIcon);
            }
            map.put(treeNode.getId(), treeNode);
        }
        for(Map.Entry<Integer,TypeTreeNode> entry: map.entrySet()){
            TypeTreeNode node=entry.getValue();
            if(map.get(node.getParentId())!=null)
                map.get(node.getParentId()).addChild(node);
        }
        for(Map.Entry<Integer,TypeTreeNode> entry:map.entrySet()){
            TypeTreeNode node=entry.getValue();
            node.nodeIsLeaf();
            if(node.isLeaf()){
                node.setState("open");
            }
        }
        ArrayList<TypeTreeNode> list = new ArrayList<TypeTreeNode>();
        for(Map.Entry<Integer, TypeTreeNode> entry : map.entrySet()){
            if(entry.getValue().getParentId()==parentId){
                list.add(entry.getValue());
            }
        }
        return new Gson().toJson(list);
    }

    @Override
    public String getAllCasesToChoose(String checkIds, HttpSession session){
//        List<Case> casess = caseMapper.selectAll();
//        List<Integer> checkIds = new ArrayList<Integer>();
//        for(Case _case : casess){
//            if(_case.getType()==caseDataType){
//                checkIds.add(_case.getId());
//            }
//        }
        List<Integer> checked = new ArrayList<Integer>();
        if(!checkIds.equals("0")){
            String[] checks = checkIds.split(",");
            for(int i=0;i<checks.length;i++){
                checked.add(Integer.parseInt(checks[i]));
            }
        }
        //add authority
        User user=(User) session.getAttribute(Constant.USER_SESSION_KEY);
        //build tree
        List<Schema> schemas = new ArrayList<Schema>();
        List<Case> cases = new ArrayList<Case>();
        List<DataRight> rights = dataRightMapper.selectByUserId(user.getId(),writeAuth);
        Set<Schema> schemaParent = new HashSet<Schema>();
        for(DataRight right : rights){
            int schemaId = right.getSchemaid();
            Schema schema = schemaMapper.selectByPrimaryKey(schemaId);
            schemas.add(schema);
            Schema parent = schemaMapper.selectByPrimaryKey(schema.getParentId());
            if(parent !=null) {
                schemaParent.add(parent);
                Queue<Schema> queue = new LinkedList<Schema>();
                queue.offer(parent);
                while (!queue.isEmpty()) {
                    Schema p = schemaMapper.selectByPrimaryKey(queue.poll().getParentId());
                    if(p!=null) {
                        schemaParent.add(p);
                        queue.offer(p);
                    }
                }
            }
            List<Case> schema_cases = caseMapper.selectAllBySchemaId(schemaId);
            cases.addAll(schema_cases);
        }
        for(Schema schema_parent : schemaParent){
            schemas.add(schema_parent);
        }
        List<BaseNode> schemaAndCases = new ArrayList<BaseNode>();
        schemaAndCases.addAll(schemas);
        for(Case _case : cases){
            schemaAndCases.add(_case);
        }
        BaseTreeNode root = webFrontHelper.buildCaseTree(schemaAndCases, checked);
        List<BaseTreeNode> list = new ArrayList<BaseTreeNode>();
        list.add(root);
        return new Gson().toJson(list);
    }

    private int getSchemaId(String[] schemaNames){
        int parentId = 0;
        Schema schema = null;
        for(int i=0;i<schemaNames.length;i++){
            String name = schemaNames[i];
            schema = schemaMapper.selectByNameAndParentId(name,parentId);
            if(schema == null) return -1;
            parentId = schema.getId();
        }
        int dataBaseId = parentId;
        return dataBaseId;
    }

    private int getCaseId(String[] caseNames, int parentId){
        Case _case = null;
        for(int i=0;i<caseNames.length;i++){
            String name = caseNames[i];
            _case = caseMapper.selectByNameAndParentId(name,parentId);
            if(_case == null) return -1;
            parentId = _case.getId();
        }
        int caseId = parentId;
        return caseId;
    }

    private int getFieldId(String[] fieldNames, int schemaId){
        Field field = null;
        int parentId = 0;
        for(int i=0;i<fieldNames.length;i++){
            String name = fieldNames[i];
            field = fieldMapper.selectByNameAndParentId(name,parentId);
            if(field == null) return -1;
            parentId = field.getId();
        }
        int fieldId = parentId;
        return fieldId;
    }

    private boolean checkName(Case _case){
        Case the_case = caseMapper.selectByPrimaryKey(_case.getId());
        List<CaseWithBLOBs> cases=caseMapper.selectByParentId(the_case.getParentId());
        //System.out.println(the_case.getParentId());
        if(cases!=null){
            for(CaseWithBLOBs s:cases){
                if(s.getType().equals(_case.getType())&&s.getName().equals(_case.getName())&&(!_case.getId().equals(s.getId())))
                    return false;
            }
        }
        return true;
    }

    private Map<Integer, String> getTypeNameMap(){
        Map<Integer, String> map = new HashMap<Integer, String>();
        map.put(fieldFloatType, fieldFloatName);
        map.put(fieldTextType, fieldTextName);
        map.put(fieldPicType, fieldPicName);
        map.put(fieldAttachType, fieldAttachName);
        map.put(field3DType, field3DName);
        map.put(fieldMatrixType, fieldMatrixName);
        map.put(fieldVideoType, fieldVideoName);
        map.put(fieldMultiType, fieldMultiName);
        map.put(fieldTimeType, fieldTimeName);
        map.put(fieldLinkType, fieldLinkName);
        return map;
    }

    private String getCasePath(int caseId){
        Case _case = caseMapper.selectByPrimaryKey(caseId);
        String path = "";
        if(_case.getSchemaId().equals(_case.getParentId())){
            Schema schema = schemaMapper.selectByPrimaryKey(_case.getSchemaId());
            path = "/"+caseId;
        }else{
            path += ((CaseWithBLOBs)caseMapper.selectByPrimaryKey(_case.getParentId())).getPath()+"/"+caseId;
        }
        return path;
    }

}
